package com.entity;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Excel批量读取
 */

public class ExcelReadPLDQ {
    public static void main(String[] args) throws IOException {
        //获取位置
        FileInputStream in = new FileInputStream("G:\\Excel练习\\写入信息.xls");
        //获取工作薄
        HSSFWorkbook workbook = new HSSFWorkbook(in);
        //获取sheet页
        HSSFSheet sheet = workbook.getSheetAt(0);
        //获取有内容的开始和结束行
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        //System.out.println(firstRowNum);
        //System.out.println(lastRowNum);
        //直接可以获取某行的开始与结束
        HSSFRow row1 = sheet.getRow(1);

       for(int j=firstRowNum;j<=lastRowNum;j++){
           Row row = sheet.getRow(j);
           if (row==null){
                continue;
           }
           //获取有内容的开始和结束单元格
           short firstCellNum = row.getFirstCellNum();
           short lastCellNum = row.getLastCellNum();
           int len = lastRowNum-firstCellNum;
           Object[] objs=new Object[len];
           for(int k = 0;k<len;k++){
                Cell cell = row.getCell(k);
                objs[k] = getCellValue(cell);
           }
           System.out.println(ArrayUtils.toString(objs));
       }
        in.close();
    }

    //获取单元格各类型值，返回字符串类型
    public static String getCellValue(Cell cell) {
        //判断是否为null或空串
        if (cell==null) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
                break;
        }
        //返回单元格值并去除首尾空格和去除空格
        return cellValue.trim().replace(" ","");
    }

}
